using System;
using System.Data.Linq;

namespace RomanJendrusz.Amazon.ETL.Infrastructure.DataBase
{
    public class DataBaseContext: IDataBaseContext
    {
        private const string BooksUpdateRowTrigger = 
                @"CREATE TRIGGER trig_insert_update_book
                ON Books
                FOR INSERT, UPDATE
                AS
                Update Books SET Updated=GetDate() WHERE [ASIN] = (SELECT [ASIN] FROM Inserted)";

        private const string CustomerReviewUpdateRowTrigger =
                @"CREATE TRIGGER trig_insert_update_Customer_Review
                ON CustomerReviews
                FOR INSERT, UPDATE
                AS
                Update CustomerReviews SET Updated=GetDate() WHERE [CustomerReviewId] = (SELECT [CustomerReviewId] FROM Inserted)";

        private const string CustomerUpdateRowTrigger =
                @"CREATE TRIGGER trig_insert_update_Customer
                ON Customers
                FOR INSERT, UPDATE
                AS
                Update Customers SET Updated=GetDate() WHERE [CustomerId] = (SELECT [CustomerId] FROM Inserted)";

        private const string AuthorUpdateRowTrigger =
                @"CREATE TRIGGER trig_insert_update_Author
                ON Authors
                FOR INSERT, UPDATE
                AS
                Update Authors SET Updated=GetDate() WHERE [AuthorId] = (SELECT [AuthorId] FROM Inserted)";

        private const string SimiliarBookUpdateRowTrigger =
                @"CREATE TRIGGER trig_insert_update_SimiliarBook
                ON SimiliarBooks
                FOR INSERT, UPDATE
                AS
                Update SimiliarBooks SET Updated=GetDate() 
                    WHERE [SimiliarBookAsin] = (SELECT [SimiliarBookAsin] FROM Inserted) 
                    AND [BookSimiliarToAsin] = (SELECT [BookSimiliarToAsin] FROM Inserted)";

        private const string ListUpdateRowTrigger =
                @"CREATE TRIGGER trig_insert_update_List
                ON Lists
                FOR INSERT, UPDATE
                AS
                Update Lists SET Updated=GetDate() 
                    WHERE [ListId] = (SELECT [ListId] FROM Inserted)";

        private const string ListItemUpdateRowTrigger =
                @"CREATE TRIGGER trig_insert_update_ListItem
                ON ListItems
                FOR INSERT, UPDATE
                AS
                Update ListItems SET Updated=GetDate() 
                    WHERE [ListItemId] = (SELECT [ListItemId] FROM Inserted)";

        private const string SellerUpdateRowTrigger =
                @"CREATE TRIGGER trig_insert_update_Seller
                ON Sellers
                FOR INSERT, UPDATE
                AS
                Update Sellers SET Updated=GetDate() 
                    WHERE [SellerId] = (SELECT [SellerId] FROM Inserted)";

        private const string SellerFeedbackUpdateRowTrigger =
                @"CREATE TRIGGER trig_insert_update_SellerFeedback
                ON SellerFeedbacks
                FOR INSERT, UPDATE
                AS
                Update SellerFeedbacks SET Updated=GetDate() 
                    WHERE [SellerFeedbackId] = (SELECT [SellerFeedbackId] FROM Inserted)";

        private const string SellerOfferUpdateRowTrigger =
                        @"CREATE TRIGGER trig_insert_update_SellerOffer
                ON SellerOffers
                FOR INSERT, UPDATE
                AS
                Update SellerOffers SET Updated=GetDate() 
                    WHERE [SellerOfferId] = (SELECT [SellerOfferId] FROM Inserted)";

        private const string BookAuthorUpdateRowTrigger =
                        @"CREATE TRIGGER trig_insert_update_BookAuthor
                ON BookAuthors
                FOR INSERT, UPDATE
                AS
                Update BookAuthors SET Updated=GetDate() 
                    WHERE [AuthorId] = (SELECT [AuthorID] FROM Inserted) AND BookAsin = (SELECT [BookAsin] FROM Inserted)";

        private readonly DataContext innerContext;
        
        public DataContext DataContext
        {
            get { return innerContext; }
        }

        public DataBaseContext(IMappingSourceBuilder sourceBuilder, IDataBaseConnectionFactory connectionFactory)
        {
            if (sourceBuilder == null) throw new ArgumentNullException("sourceBuilder");
            if (connectionFactory == null) throw new ArgumentNullException("connectionFactory");
            innerContext = new DataContext(connectionFactory.DBConnection, sourceBuilder.MappingSource);
        }

        public void CreateDatabase()
        {
            innerContext.CreateDatabase();
            innerContext.ExecuteQuery<int>(BooksUpdateRowTrigger);
            innerContext.ExecuteQuery<int>(CustomerReviewUpdateRowTrigger);
            innerContext.ExecuteQuery<int>(CustomerUpdateRowTrigger);
            innerContext.ExecuteQuery<int>(AuthorUpdateRowTrigger);
            innerContext.ExecuteQuery<int>(SimiliarBookUpdateRowTrigger);
            innerContext.ExecuteQuery<int>(ListUpdateRowTrigger);
            innerContext.ExecuteQuery<int>(ListItemUpdateRowTrigger);
            innerContext.ExecuteQuery<int>(SellerUpdateRowTrigger);
            innerContext.ExecuteQuery<int>(SellerFeedbackUpdateRowTrigger);
            innerContext.ExecuteQuery<int>(SellerOfferUpdateRowTrigger);
            innerContext.ExecuteQuery<int>(BookAuthorUpdateRowTrigger);
        }

        public void DeleteDatabase()
        {
            innerContext.DeleteDatabase();
        }

        public bool DataBaseExists
        {
            get { return innerContext.DatabaseExists(); }
        }

        public void Dispose()
        {
            innerContext.Dispose();
        }
    }
}